package com.lz.sports.db

import android.text.TextUtils
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.sqlite.db.SimpleSQLiteQuery
import com.lz.library.extension.APP
import com.lz.sports.LogUtils
import com.lz.sports.R
import com.lz.sports.bean.*
import com.lz.sports.bean.ExamResult

@Database(
    entities = [
        Examinee::class, ExamInspectGroup::class, ExamInspect::class,
        ExamSignIn::class, ExamSure::class, ExamResult::class
    ],
    version = 1
)
abstract class ExamineeDatabase : RoomDatabase() {

    companion object {
        private const val DB_NAME = "Examinee.db"

        private var INSTANCE: ExamineeDatabase? = null

        fun get(): ExamineeDatabase {
            if (INSTANCE == null) {
                synchronized(ExamineeDatabase::class.java) {
                    if (INSTANCE == null)
                        INSTANCE = Room.databaseBuilder(APP, ExamineeDatabase::class.java, DB_NAME)
                            .allowMainThreadQueries()
                            .build()
                }
            }
            return INSTANCE!!
        }
    }

    //考生表
    abstract fun getExamineeDao(): ExamineeDao

    //检录表
    abstract fun getExamInspectDao(): ExamInspectDao

    //清空所有表
    fun clearAll() {
        getExamineeDao().clearExamSign()
        getExamineeDao().clearExamSure()
        getExamineeDao().clearExamResult()

        getExamInspectDao().clearExamInspect()
        getExamInspectDao().clearExamInspectGroup()

        getExamineeDao().clearExaminee()
    }

    //查询所有
    fun queryAll(
        signed: Boolean,
        inspected: Boolean,
        examined: Boolean,
        sure: Boolean,
        keyword: String,
        examPlanId: Int,
        examAreaId: Int,
        examSiteId: Int
    ): List<QueryData> {
        if (!signed && !inspected && !examined && !sure) return emptyList()
        val list = ArrayList<QueryData>()
        if (signed) {
            signInList(keyword, examPlanId, examAreaId, examSiteId).filter { !it.isInvalid() }
                .takeIf { it.isNotEmpty() }?.also { list.addAll(it) }

        }
        if (inspected) {
            inspectList(keyword, examPlanId, examAreaId, examSiteId).filter { !it.isInvalid() }
                .takeIf { it.isNotEmpty() }?.also { list.addAll(it) }
        }
        if (examined) {
            examineeList(keyword, examPlanId, examAreaId, examSiteId).filter { !it.isInvalid() }
                .takeIf { it.isNotEmpty() }?.also { list.addAll(it) }
        }
        if (sure) {
            sureList(keyword, examPlanId, examAreaId, examSiteId).filter { !it.isInvalid() }
                .takeIf { it.isNotEmpty() }?.also { list.addAll(it) }
        }
        return list
    }

    //----------------------------  考生, 关联项目成绩表, 签到, 成绩确认  ----------------------------

    //插入考生列表
    fun insertExaminee(examineeList: List<Examinee>) {
        getExamineeDao().insertExaminee(examineeList)
    }

    //插入考生关联项目成绩表(插入考生时)
    fun insertExamResult(examResults: List<ExamResult>) {
        getExamineeDao().insertExamResult(examResults)
    }

    //插入成绩确认表
    fun insertExamSure(examSureList: List<ExamSure>) {
        getExamineeDao().insertExamSure(examSureList)
    }

    //考生是否存在
    fun hasExaminee(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int, examineesNo: String, idCard: String): Long =
        getExamineeDao().hasExaminee(examPlanId, projectId, examAreaId, examSiteId, examineesNo, idCard)

    //添加学生
    fun addExaminee(examinee: Examinee, examProject: ExamProject) {
        getExamineeDao().addExaminee(examinee)
        getExamineeDao().addExamResult(ExamResult(examinee, examProject))
    }

    //查询考区考点内未检录的考生数量
    fun examineeUnInspectCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long =
        getExamineeDao().examineeUnInspectCount(examPlanId, projectId, examAreaId, examSiteId)

    //通过idCard查找学生
    fun findExamineeByIdCard(examPlanId: Int, examAreaId: Int, examSiteId: Int, idCard: String): Examinee? =
        getExamineeDao().findExamineeByIdCard(examPlanId, examAreaId, examSiteId, idCard)

    //找出考生项目成绩
    fun findExamResult(
        examineeId: Int,
        examPlanId: Int,
        projectId: Int,
        examAreaId: Int,
        examSiteId: Int
    ): ExamResult? =
        getExamineeDao().findExamResult(examineeId, examPlanId, projectId, examAreaId, examSiteId)

    //查询考生对应的项目表
    fun examResultList(examPlanId: Int, examAreaId: Int, examSiteId: Int, examineeId: Int): List<ExamResult> =
        getExamineeDao().examResultList(examPlanId, examAreaId, examSiteId, examineeId)

    //查询考生成绩表, 筛选条件
    fun examResults(
        inspectState: Int,
        examineeState: Int,
        uploadState: Int,
        examPlanId: Int,
        examAreaId: Int,
        examSiteId: Int,
        keyword: String?
    ): List<ExamResult> = if (TextUtils.isEmpty(keyword)) examResultFilter(
        inspectState,
        examineeState,
        uploadState,
        examPlanId,
        examAreaId,
        examSiteId
    ) else examResultFilter(
        inspectState, examineeState, uploadState, examPlanId, examAreaId, examSiteId, keyword!!
    )

    fun lackExamResults(list: List<ExamResult>) {
        getExamineeDao().updateExamResult(list)
    }

    //查询考生成绩, 通过检录, 考试, 上传状态筛选
    private fun examResultFilter(
        inspectState: Int,
        examineeState: Int,
        uploadState: Int,
        examPlanId: Int,
        examAreaId: Int,
        examSiteId: Int
    ): List<ExamResult> {
        val args = ArrayList<String>().apply {
            add("examPlanId = $examPlanId")
            add("examAreaId = $examAreaId")
            add("examSiteId = $examSiteId")
        }
        when (inspectState) {
            1 -> args.add("inspectStatus = 0")
            2 -> args.add("inspectStatus = -1")
        }
        when (examineeState) {
            1 -> args.add("score != -1 and lackStatus = -1")
            2 -> args.add("score = -1 and lackStatus = -1")
            3 -> args.add("lackStatus = 0")
        }
        when (uploadState) {
            1 -> args.add("uploadStatus = 0")
            2 -> args.add("uploadStatus = -1")
        }
        val selectArgs = StringBuilder()
        for (index in 0 until args.size - 1) {
            selectArgs.append(args[index]).append(" and ")
        }
        selectArgs.append(args[args.size - 1])
        val sql = "select * from ExamResult where $selectArgs"
        LogUtils.i("sql = $sql")
        return getExamineeDao().examResultWithInfo(SimpleSQLiteQuery(sql)).mapNotNull { it.resultWithInfo() }
    }

    //查询考生成绩, 通过检录, 考试, 上传状态筛选
    private fun examResultFilter(
        inspectState: Int,
        examineeState: Int,
        uploadState: Int,
        examPlanId: Int,
        examAreaId: Int,
        examSiteId: Int,
        keyword: String
    ): List<ExamResult> {
        val args = ArrayList<String>().apply {
            add("er.examPlanId = $examPlanId")
            add("er.examAreaId = $examAreaId")
            add("er.examSiteId = $examSiteId")
        }
        when (inspectState) {
            1 -> args.add("er.inspectStatus = 0")
            2 -> args.add("er.inspectStatus = -1")
        }
        when (examineeState) {
            1 -> args.add("er.score != -1 and er.lackStatus = -1")
            2 -> args.add("er.score = -1 and er.lackStatus = -1")
            3 -> args.add("er.lackStatus = 0")
        }
        when (uploadState) {
            1 -> args.add("er.uploadStatus = 0")
            2 -> args.add("er.uploadStatus = -1")
        }
        args.add("(e.studentName like '%' || $keyword || '%' or e.examineesNo like '%' || $keyword || '%' or e.idCard like '%' || $keyword || '%')")
        val selectArgs = StringBuilder()
        for (index in 0 until args.size - 1) {
            selectArgs.append(args[index]).append(" and ")
        }
        selectArgs.append(args[args.size - 1])
        val sql = "select er.* from ExamResult er left join Examinee e on er.examineeId = e.id where $selectArgs"
        LogUtils.i("sql = $sql")
        return getExamineeDao().examResultWithInfo(SimpleSQLiteQuery(sql)).mapNotNull { it.resultWithInfo() }
    }

    //批量更新考生的检录状态
    fun updateExamInspectStatus(
        examPlanId: Int,
        projectId: Int,
        examAreaId: Int,
        examSiteId: Int,
        examineeIds: List<Int>
    ) {
        getExamineeDao().updateExamInspectStatus(examPlanId, projectId, examAreaId, examSiteId, examineeIds)
    }

    //签到
    fun signIn(signIn: ExamSignIn): Long = getExamineeDao().signIn(signIn)

    //查询考生成绩和检录
    fun examineeWithResultInspect(examPlanId: Int, examAreaId: Int, examSiteId: Int, idCard: String): Examinee? {
        val res = getExamineeDao().examineeWithResults(examPlanId, examAreaId, examSiteId, idCard)
        if (res == null || res.isInvalid()) return null
//        LogUtils.i("examinee = ${res.examinee!!.studentName}, ${res.examinee!!.id}")
        res.resultList!!.forEach {
            it.inspectList =
                getExamInspectDao().examInspectList(examPlanId, it.projectId, examAreaId, examSiteId, res.examinee!!.id)
        }
        res.examinee!!.examSignIn = getExamineeDao().findSignIn(res.examinee!!.id, examPlanId, examAreaId, examSiteId)
//        LogUtils.i("examineeSign = ${res.examinee!!.examSignIn?.examineeId}")
        return res.examinee!!.apply { examResults = res.resultList }
    }

    fun signInList(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSignInWithInfo> =
        if (TextUtils.isEmpty(keyword)) getExamineeDao().querySignIn(examPlanId, examAreaId, examSiteId)
        else getExamineeDao().querySignIn(keyword, examPlanId, examAreaId, examSiteId)

    fun sureList(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamSureWithInfo> =
        if (TextUtils.isEmpty(keyword)) getExamineeDao().querySure(examPlanId, examAreaId, examSiteId)
        else getExamineeDao().querySure(keyword, examPlanId, examAreaId, examSiteId)

    //----------------------------  考生检录  ----------------------------

    //插入检录列表信息
    fun insertExamInspectList(inspectList: List<ExamInspect>, inspectGroup: ExamInspectGroup) {
        inspectGroup.id = getExamInspectDao().insertInspectGroup(inspectGroup).toInt()
        inspectList.forEach { it.inspect(inspectGroup); it.tmpRenameFile() }
        getExamInspectDao().insertInspectList(inspectList)
    }

    //查找检录组
    fun inspectGroupList(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): List<ExamInspectGroup> {
        val list = getExamInspectDao().inspectGroup(examPlanId, projectId, examAreaId, examSiteId)
        for (index in list.indices) {
            val group = list[index]
            group.examName = APP.resources.getString(R.string.exam_group_index, index + 1)
            group.initInfo(getExamInspectDao().queryInspect(group.id))
        }
        return list
    }

    //保存考试成绩
    fun saveExamineeResult(inspectGroup: ExamInspectGroup) {
        getExamInspectDao().updateInspectGroup(inspectGroup)
        getExamInspectDao().updateExamInspectList(inspectGroup.inspectList)
        //查询该组考生的最大数据并保存到最终成绩中

        val resultList = ArrayList<ExamResult>()
        inspectGroup.inspectList.forEach { //查询最佳成绩并设置到成绩表中
            val res = getExamineeDao().findExamResult(it.examineeId, it.examPlanId, it.projectId, it.examAreaId, it.examSiteId)
            if (res != null) {
                res.saveResult(it)
                resultList.add(res)
            }
        }
        if (resultList.isNotEmpty()) getExamineeDao().updateExamResult(resultList)
    }

    //查询检录组未考大小
    fun inspectGroupCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long =
        getExamInspectDao().inspectGroupCount(examPlanId, projectId, examAreaId, examSiteId)

    //查询已检录未考的人数
    fun inspectCount(examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long =
        getExamInspectDao().inspectCount(examPlanId, projectId, examAreaId, examSiteId)

    //查询学生检录次数
    fun inspectCount(examineeId: Int, examPlanId: Int, projectId: Int, examAreaId: Int, examSiteId: Int): Long =
        getExamInspectDao().inspectCount(examineeId, examPlanId, projectId, examAreaId, examSiteId)

    //查询检录
    fun inspectList(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExamInspectWithInfo> =
        if (TextUtils.isEmpty(keyword)) getExamInspectDao().queryInspect(examPlanId, examAreaId, examSiteId)
        else getExamInspectDao().queryInspect(keyword, examPlanId, examAreaId, examSiteId)

    //查询考试列表
    fun examineeList(keyword: String, examPlanId: Int, examAreaId: Int, examSiteId: Int): List<ExaminedWithInfo> =
        if (TextUtils.isEmpty(keyword)) getExamInspectDao().queryExamined(examPlanId, examAreaId, examSiteId)
        else getExamInspectDao().queryExamined(keyword, examPlanId, examAreaId, examSiteId)

    //保存检录或者考试状态
    fun updateExamInspectList(inspectList: List<ExamInspect>) {
        getExamInspectDao().updateExamInspectList(inspectList)
    }

    //更新签到表
    fun updateExamSignList(signInList: List<ExamSignIn>) {
        getExamineeDao().updateExamSignList(signInList)
    }

    //更新最终成绩表状态
    fun updateExamResultList(resultList: List<ExamResult>) {
        getExamineeDao().updateExamResultList(resultList)
    }

    //更新成绩确认
    fun updateExamSureList(sureList: List<ExamSure>) {
        getExamineeDao().updateExamSureList(sureList)
    }


    // ---------------------------      上传   检录 ,考试     -------------------------

    //查询所有未上传的考试信息
    fun examUploadList(): List<ExamInspect> = getExamInspectDao().examUploadList()

    //查询所有未上传的检录信息
    fun inspectUploadList(): List<ExamInspect> = getExamInspectDao().uploadList()

    //查询报考照未传
    fun inspectExamPhotoUploadList(): List<ExamInspect> = getExamInspectDao().examPhotoUploadList()

    //查询身份证照未传
    fun inspectIdCardPhotoUploadList(): List<ExamInspect> = getExamInspectDao().idCardPhotoUploadList()

    //查询现场照未传
    fun inspectScenePhotoUploadList(): List<ExamInspect> = getExamInspectDao().scenePhotoUploadList()


    // ---------------------------    上传     签到      -------------------------

    //查询所有未上传的检录信息
    fun signUploadList(): List<ExamSignIn> = getExamineeDao().signUploadList()

    //查询报考照未传
    fun signExamPhotoUploadList(): List<ExamSignIn> = getExamineeDao().signExamPhotoUploadList()

    //查询身份证照未传
    fun signIdCardPhotoUploadList(): List<ExamSignIn> = getExamineeDao().signIdCardPhotoUploadList()

    //查询现场照未传
    fun signScenePhotoUploadList(): List<ExamSignIn> = getExamineeDao().signScenePhotoUploadList()

    //查询所有未上传的成绩
    fun resultUploadList(): List<ExamResult> = getExamineeDao().resultUploadList()

    //查询所有未上传的成绩
    fun lackUploadList(): List<ExamResult> = getExamineeDao().lackUploadList()

    //成绩确认未上传
    fun sureUploadList(): List<ExamSure> = getExamineeDao().sureUploadList()

    //查询报考照未传
    fun sureExamPhotoUploadList(): List<ExamSure> = getExamineeDao().sureExamPhotoUploadList()

    //查询电子签名照未传
    fun sureSignaturePhotoUploadList(): List<ExamSure> = getExamineeDao().sureSignaturePhotoUploadList()

    //查询身份证照未传
    fun sureIdCardPhotoUploadList(): List<ExamSure> = getExamineeDao().sureIdCardPhotoUploadList()

    //查询现场照未传
    fun sureScenePhotoUploadList(): List<ExamSure> = getExamineeDao().sureScenePhotoUploadList()
}